﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using emp.Model.SysManage;
using System.Data;
using Net.SourceForge.Koogra.Excel;
using System.Data.SqlClient;
using emp.SqlServerDAL;
using System.Configuration;
using System.Net.Mail;
using System.Text.RegularExpressions;




public partial class crm_salienceMember_SalienceMemberUpload : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    public static DataTable ExcelToDS(string path)
    {
        Workbook wb = new Workbook(path);
        System.Data.DataTable dt = new System.Data.DataTable();
        foreach (Worksheet ws1 in wb.Sheets)
        {
            Worksheet ws = wb.Sheets[0];
            string[] str = new string[ws.Rows[0].Cells.LastCol + 1];
            for (int i = 0; i < ws.Rows[0].Cells.LastCol + 1; i++)
            {
                str[i] = ws.Rows[0].Cells[(byte)i].Value.ToString();
                dt.Columns.Add(ws.Rows[0].Cells[(byte)i].Value.ToString(), typeof(string));
            }
            for (int r = ws.Rows.FirstRow; r <= ws.Rows.LastRow; ++r)
            {
                Row row = ws.Rows[(ushort)r];
                if (row != null)
                {

                    dt.Rows.Add(r);
                    for (int c = row.Cells.FirstCol; c <= row.Cells.LastCol; ++c)
                    {
                        Net.SourceForge.Koogra.Excel.Cell cell = row.Cells[(byte)c];
                        if (cell != null)
                        {
                            dt.Rows[r][str[c]] = cell.Value;
                        }
                    }
                }
            }


        }
        dt.Rows[0].Delete();
        return dt;
    }

    private  bool IsMobile(string source)
    {
        return Regex.IsMatch(source, @"^1[123456789]\d{9}$", RegexOptions.IgnoreCase);
    }



    private  bool IsDH(string str)
    {
        bool bo = true;
        string sql = "select *from TASTE_CAR_INFORMATION where TCI_TELEPHONE='" + str + "'";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }



    private bool IsPRO(string str)
    {
        bool bo = true;
        string sql = "select qsrd_uid,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_parent ) as pname  ,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_child) as cname from QUEST_STD_RELATE_DETAIL  where qsrd_qsr_uid='1'   and qsrd_qsa_uid_parent in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + str + "')";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }


    private bool IsCRTY(string str,string pro)
    {
        bool bo = true;
        string sql = "select qsrd_uid,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_parent ) as pname  ,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_child) as cname from QUEST_STD_RELATE_DETAIL  where qsrd_qsr_uid='1'   and qsrd_qsa_uid_parent in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + pro + "')  and qsrd_qsa_uid_child in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + str + "')";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }

    private bool IsDER(string str,string city)
    {
        bool bo = true;
        string sql = "select qsrd_uid,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_parent) as pname  ,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_child) as cname from QUEST_STD_RELATE_DETAIL  where qsrd_qsr_uid='9' and qsrd_qsa_uid_parent in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + city + "') and qsrd_qsa_uid_child in(select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + str + "')";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }



    private bool IsPP(string str)
    {
        bool bo = true;
        string sql = "select qsrd_uid,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_parent ) as pname  ,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_child) as cname from QUEST_STD_RELATE_DETAIL  where qsrd_qsr_uid='2'   and qsrd_qsa_uid_parent in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='"+str+"')";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }


    private bool IsXH(string str, string pp)
    {
        bool bo = true;
        string sql = "select qsrd_uid,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_parent) as pname  ,(select qsa_answer_name from QUEST_STD_ANSWER  where qsa_uid=qsrd_qsa_uid_child) as cname from QUEST_STD_RELATE_DETAIL  where qsrd_qsr_uid='2' and qsrd_qsa_uid_parent in (select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + pp + "') and qsrd_qsa_uid_child in(select qsa_uid from QUEST_STD_ANSWER where qsa_answer_name='" + str + "')";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }



    private bool IsCX(string str)
    {
        bool bo = true;
        string sql = "SELECT  QSA_UID ,QSA_QSD_UID,QSA_NUMBER,isnull(QSA_ANSWER_NAME,''),isnull(QSA_ANSWER_VALUE,''),QSA_STATUS FROM QUEST_STD_ANSWER   where QSA_QSD_UID ='190' and QSA_ANSWER_NAME='" + str + "'";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }

    private bool IsSJ(string str)
    {
        bool bo = true;
        string sql = "SELECT  QSA_UID ,QSA_QSD_UID,QSA_NUMBER,isnull(QSA_ANSWER_NAME,''),isnull(QSA_ANSWER_VALUE,''),QSA_STATUS FROM QUEST_STD_ANSWER   where QSA_QSD_UID ='35' and QSA_ANSWER_NAME='" + str + "'";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }

    private bool IsYS(string str)
    {
        bool bo = true;
        string sql = "SELECT  QSA_UID ,QSA_QSD_UID,QSA_NUMBER,isnull(QSA_ANSWER_NAME,''),isnull(QSA_ANSWER_VALUE,''),QSA_STATUS FROM QUEST_STD_ANSWER   where QSA_QSD_UID ='36' and QSA_ANSWER_NAME='" + str + "'";
        System.Data.DataTable dt1 = getTableInfo(sql);

        if (dt1.Rows.Count > 0)
        {
            bo = false;
        }

        return bo;
    }

    private bool Yz2(System.Data.DataTable dt)
    {
        bool bo = true;

        //string SQLConnString = ConfigurationSettings.AppSettings["SQLConnString1"];

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i][0] != null && dt.Rows[i][0].ToString() != "")
            {
                if (dt.Rows[i][0].ToString().Length > 50)
                {
                    UpRxcel(dt, "姓名有误!",i);
                    //Label1.Text = (i + 1) + "列：" + "姓名有误！";
                    bo = false;
                }



            }
            else
            {
                UpRxcel(dt, "姓名不能为空!",i);
               // Label1.Text = (i + 1) + "列：" + "姓名不能为空！";
                bo = false;
            }
            if (dt.Rows[i][1] != null && dt.Rows[i][1].ToString() != "")
            {
                if (dt.Rows[i][1].ToString() != "M" && dt.Rows[i][1].ToString() != "F")
                {
                    UpRxcel(dt, "性别只能是‘M’或‘F’!",i);
                    //Label1.Text = (i + 1) + "列：" + "性别只能是‘男’或‘女’！";
                    bo = false;
                }

            }
            else
            {
                UpRxcel(dt, "性别不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "性别不能为空！";
                bo = false;
            }
            if (dt.Rows[i][2] != null && dt.Rows[i][2].ToString() != "")
            {
                if (!IsMobile(dt.Rows[i][2].ToString()))
                {
                    UpRxcel(dt, "联系电话有误!",i);
                   // Label1.Text = (i + 1) + "列：" + "联系电话有误！";
                    bo = false;
                }



                if (!IsDH(dt.Rows[i][2].ToString()))
                {
                    UpRxcel(dt, "联系电话已存在!", i);
                    // Label1.Text = (i + 1) + "列：" + "联系电话有误！";
                    bo = false;
                }


            }
            else
            {
                UpRxcel(dt, "联系电话不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "联系电话不能为空！";
                bo = false;
            }
            if (dt.Rows[i][3] != null && dt.Rows[i][3].ToString() != "")
            {
                string[] listc = dt.Rows[i][3].ToString().Split(';');

                for (int j = 0; j < listc.Length; j++)
                {
                    if (listc[j].ToString()!="")
                    {
                        if (IsCX(listc[j].ToString().Replace(";", "")))
                        {
                            UpRxcel(dt, "意向车型有误!", i);
                            //Label1.Text = (i + 1) + "列：" + "意向车型有误！";
                            bo = false;
                            break;
                        }
                    }
                    
                   
                }
                





            }
            else
            {
                //UpRxcel(dt, "意向车型不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "意向车型不能为空！";
                //bo = false;

            }
            if (dt.Rows[i][4] != null && dt.Rows[i][4].ToString() != "")
            {
                if (IsSJ(dt.Rows[i][4].ToString()))
                {
                    UpRxcel(dt, "购车计划有误!",i);
                   // Label1.Text = (i + 1) + "列：" + "购车计划有误！";
                    bo = false;
                }

            }
            else
            {
                UpRxcel(dt, "购车计划时间不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "购车计划时间不能为空！";
                bo = false;
            }
            if (dt.Rows[i][5] != null && dt.Rows[i][5].ToString() != "")
            {
                if (IsYS(dt.Rows[i][5].ToString()))
                {
                    UpRxcel(dt, "购车预算有误!",i);
                    //Label1.Text = (i + 1) + "列：" + "购车预算有误！";
                    bo = false;
                }

            }
            else
            {
                UpRxcel(dt, "购车预算时间不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "购车预算时间不能为空！";
                bo = false;
            }

            if (dt.Rows[i][6] != null && dt.Rows[i][6].ToString() != "")
            {
                if (dt.Rows[i][6].ToString() != "Y" && dt.Rows[i][6].ToString() != "N")
                {
                    UpRxcel(dt, "是否同意经销商只能是‘Y’或‘N’!",i);
                    //Label1.Text = (i + 1) + "列：" + "是否同意经销商只能是‘是’或‘否’！";
                    bo = false;
                }
            }
            else
            {
                UpRxcel(dt, "是否同意经销商联系不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "是否同意经销商联系不能为空！";
                bo = false;
            }


            if (dt.Rows[i][7] != null && dt.Rows[i][7].ToString() != "")
            {
                if (dt.Rows[i][7].ToString() != "Y" && dt.Rows[i][7].ToString() != "N")
                {
                    UpRxcel(dt, "是否申请试乘试驾只能是‘Y’或‘N’!", i);
                    //Label1.Text = (i + 1) + "列：" + "是否同意经销商只能是‘是’或‘否’！";
                    bo = false;
                }
            }
            else
            {
                UpRxcel(dt, "是否申请试乘试驾不能为空!", i);
                //Label1.Text = (i + 1) + "列：" + "是否同意经销商联系不能为空！";
                bo = false;
            }



            if (dt.Rows[i][8] != null && dt.Rows[i][8].ToString() != "")
            {
                if (dt.Rows[i][8].ToString() != "Y" && dt.Rows[i][8].ToString() != "Y")
                {
                    UpRxcel(dt, "是否申请产品手册只能是‘Y’或‘Y’!", i);
                    //Label1.Text = (i + 1) + "列：" + "是否同意经销商只能是‘是’或‘否’！";
                    bo = false;
                }
            }
            else
            {
                UpRxcel(dt, "是否申请产品手册不能为空!", i);
                //Label1.Text = (i + 1) + "列：" + "是否同意经销商联系不能为空！";
                bo = false;
            }

            if (dt.Rows[i][9] != null && dt.Rows[i][9].ToString() != "")
            {
                if (IsPRO(dt.Rows[i][9].ToString()))
                {
                    UpRxcel(dt, "所在省有误!",i);
                   // Label1.Text = (i + 1) + "列：" + "所在省有误！";
                    bo = false;
                }

            }
            else
            {
                UpRxcel(dt, "所在省不能为空!",i);
               // Label1.Text = (i + 1) + "列：" + "所在省不能为空！";
                bo = false;
            }
            if (dt.Rows[i][10] != null && dt.Rows[i][10].ToString() != "")
            {
                if (IsCRTY(dt.Rows[i][10].ToString(), dt.Rows[i][9].ToString()))
                {
                    UpRxcel(dt, "所在市有误!",i);
                    //Label1.Text = (i + 1) + "列：" + "所在市有误！";
                    bo = false;
                }

            }
            else
            {
                UpRxcel(dt, "所在市不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "所在市不能为空！";
                bo = false;
            }
            if (dt.Rows[i][11] != null && dt.Rows[i][11].ToString() != "")
            {

                if (IsDER(dt.Rows[i][11].ToString(),dt.Rows[i][10].ToString()))
                {
                    UpRxcel(dt, "意向经销商名称有误!", i);
                    //Label1.Text = (i + 1) + "列：" + "意向经销商不能为空！";
                    bo = false;
                }
                

            }
            else
            {
                UpRxcel(dt, "意向经销商不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "意向经销商不能为空！";
                bo = false;
            }

            if (dt.Rows[i][12] != null && dt.Rows[i][12].ToString() != "")
            {
                if (IsPP(dt.Rows[i][12].ToString()))
                {
                    UpRxcel(dt, "现有车品牌有误!",i);
                   // Label1.Text = (i + 1) + "列：" + "现有车品牌有误！";
                    bo = false;
                }

            }
            else
            {
                //UpRxcel(dt, "现有车品牌不能为空!",i);
                //Label1.Text = (i + 1) + "列：" + "现有车品牌不能为空！";
                //bo = false;
            }

            if (dt.Rows[i][13] != null && dt.Rows[i][13].ToString() != "")
            {
                if (IsXH(dt.Rows[i][13].ToString(), dt.Rows[i][12].ToString()))
                {
                    UpRxcel(dt, "现有车型号有误!",i);
                   // Label1.Text = (i + 1) + "列：" + "现有车型号有误！";
                    bo = false;
                }

            }
            else
            {
                //UpRxcel(dt, "现有车型号不能为空!",i);
               // Label1.Text = (i + 1) + "列：" + "现有车型号不能为空！";
               // bo = false;
            }

            if (dt.Rows[i][14] != null && dt.Rows[i][14].ToString() != "")
            {
                if (dt.Rows[i][14].ToString().Length > 500)
                {
                    UpRxcel(dt, "备注有误!",i);
                    //Label1.Text = (i + 1) + "列：" + "备注有误！";
                    bo = false;
                }

            }
            //else
            //{
            //    UpRxcel(dt, "备注不能为空!", i + 1);
            //    //Label1.Text = (i + 1) + "列：" + "备注不能为空！";
            //    return false;
            //}

        }









        return bo;
    }


    private bool Yz3(System.Data.DataTable dt)
    {
        bool bo = true;
        try
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string sql15 = " INSERT INTO TASTE_CAR_INFORMATION_BAK(TCI_NAME      ,TCI_GANDER      ,TCI_TELEPHONE      ,TCI_CAR_MODEL      ,TCI_PLAN_TIME      ,TCI_BUDGET_PUR      ,TCI_IS_CONTACT ,TCI_IS_TEST     ,TCI_PROVINCE      ,TCI_CITY      ,TCI_DEALER_INTENTION      ,TCI_BRAND_OWNED      ,TCI_MODEL_OWNED      ,TCI_NOTES      ,TCI_TCB_ID)VALUES('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','" + dt.Rows[i][13].ToString() + "','') ";
                bool bo1 = ExecSql(sql15);
            }
        }
        catch 
        {
            Label1.Text = "导入表格数据格式有误，数据无法读入数据库请整理后再次导入！";
            bo = false;
        }


        string sql151 = " delete from TASTE_CAR_INFORMATION_BAK ";
        ExecSql(sql151);
        return bo;
    }




    private bool Yz(System.Data.DataTable dt) 
    {
        bool bo = true;

        if (dt.Columns.Count == 15 || dt.Columns.Count == 16)
        {
            
        }
        else 
        {
            Label1.Text = "导入表格列数有误，正确的列数为15或16！";
            return false;
        }

        if (dt == null)
        {
            Label1.Text = "导入表格格式有误，数据无法读取请整理后再次导入！";
            return false;
        }

        return bo;
    }

    //创建DataTable
    private DataTable UpRxcel(System.Data.DataTable dt, string wt, int i)
    {
        DataRow drEmployee = dt.Rows[i];
        drEmployee.BeginEdit();
        drEmployee["remark"] = drEmployee["remark"].ToString() + wt;
        drEmployee.EndEdit();


        return dt;
    }


    protected void upLaodOK_Click(object sender, EventArgs e)
    {
        string fileExt = "";
        Label1.Text = "";
        string uploadTime = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");//获得上传时间
        if (FileUpload1.HasFile)
        {

            fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);

            if (fileExt == ".xls")
            {
                int fu1 = FileUpload1.PostedFile.ContentLength/1024;
                if (fu1 > 2048 )
                {
                    Label1.Text = "Excel太大！";
                    return;
                }
                try
                {
                    int boo = 0;
                    string patex = HttpContext.Current.Server.MapPath("excel/");
                    FileUpload1.SaveAs(patex + uploadTime + fileExt);
                    System.Data.DataTable dt = ExcelToDS(patex + uploadTime + fileExt);//根据上传的文件名读取Excel文件将数据放入DataSet中


                    if (Yz(dt))
                    {
                        //创建table的第一列
                        DataColumn dc = new DataColumn();
                        //该列的数据类型
                        dc.DataType = System.Type.GetType("System.String");
                        //该列得名称
                        dc.ColumnName = "remark";
                        dt.Columns.Add(dc);

                        UserInfo myUserInfo = new UserInfo();
                        if ((UserInfo)Session["USER_KEY"] != null)
                        {
                            myUserInfo = (UserInfo)Session["USER_KEY"];

                        }
                        if (Yz2(dt))
                        {

                            if (Yz3(dt))
                            {
                                string sql15 = "INSERT INTO TASTE_CAR_BATCH(TCB_XLS_FILE,TCB_STATUS,TCB_VALID_NUM,TCB_DOLOAD_USER,TCB_DOLOAD_DATE,TCB_REMARK)VALUES('" + uploadTime + ".xls','1','" + dt.Rows.Count + "','" + myUserInfo.UserID.ToString() + "',getdate(),'导入完成') ";
                                bool bo = ExecSql(sql15);

                                string sql = "select *from TASTE_CAR_BATCH where TCB_XLS_FILE='" + uploadTime + ".xls'";
                                System.Data.DataTable dt1 = getTableInfo(sql);
                                string tid = "0";
                                if (dt1.Rows.Count>0)
                                {
                                    tid = dt1.Rows[0]["TCB_ID"].ToString();
                                }
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {

                                    //string sql1 = "select * from [SYSTEM_USER_TBL] where [user_name]='" + dt.Rows[i][10].ToString() + "'";
                                    //System.Data.DataTable dt11 = getTableInfo(sql1);
                                    string str8 = "";
                                    if (dt.Rows[i][8].ToString() == "Y")
                                    {
                                        str8 = "需要";
                                    }
                                    else 
                                    {
                                        str8 = "不需要";
                                    }


                                    string sql16 = " INSERT INTO TASTE_CAR_INFORMATION(TCI_NAME      ,TCI_GANDER      ,TCI_TELEPHONE      ,TCI_CAR_MODEL      ,TCI_PLAN_TIME      ,TCI_BUDGET_PUR      ,TCI_IS_CONTACT  ,TCI_IS_TEST    ,TCI_PROVINCE      ,TCI_CITY      ,TCI_DEALER_INTENTION      ,TCI_BRAND_OWNED      ,TCI_MODEL_OWNED      ,TCI_NOTES      ,TCI_TCB_ID,TCI_UP_USER,TCI_DATE,TCI_IS_MANUAL)VALUES('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','" + dt.Rows[i][13].ToString() + "','" + dt.Rows[i][14].ToString() + "','" + tid + "','" + myUserInfo.UserID.ToString() + "',getdate(),'" + str8 + "') ";
                                    bool bo1 = ExecSql(sql16);
                                }



                                string sql151 = "exec p_impost_taste_car '" + tid + "'";
                                bool bo111 = ExecSql(sql151);



                                Response.Redirect("TasteList.aspx", true);
                            }

                        }
                        else 
                        {
                            string[] strsz = { "姓名", "性别", "联系电话", "意向车型", "购车计划时间", "购车预算", "是否同意经销商联系", "是否申请试乘试驾", "是否申请产品手册", "所在省", "所在市", "意向经销商", "现有车品牌", "现有车型号", "备注", "错误提示" };
                            string subStr = "cw" + uploadTime;

                            emp.Utility.ExcelSaveFile.ExcelSaves(dt, strsz, patex, subStr);

                            string sql15 = "INSERT INTO TASTE_CAR_BATCH(TCB_XLS_FILE,TCB_STATUS,TCB_VALID_NUM,TCB_DOLOAD_USER,TCB_DOLOAD_DATE,TCB_REMARK)VALUES('" + "cw" + uploadTime + ".xls','0','" + dt.Rows.Count + "','" + myUserInfo .UserID.ToString()+ "',getdate(),'数据有误') ";
                            bool bo = ExecSql(sql15);

                            Response.Redirect("TasteList.aspx", true);

                        }

                    }
                    
                }

                catch (Exception ex)
                {

                    Label1.Text = "发生错误：" + ex.Message.ToString();

                }

            }
            else
            {
                Label1.Text = "只允许上传xls文件！";
            }
        }
        else
        {
            Label1.Text = "请选择要上传的文件！";
        } 
        
    }

    private  DataTable getTableInfo(string sql)
    {
        DataSet data = new DataSet();
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            try
            {
                sda.SelectCommand = new SqlCommand();
                sda.SelectCommand.CommandText = sql;
                sda.SelectCommand.Connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                sda.Fill(data);
            }
            catch { }
        }
        return data.Tables[0];
    }


    private bool ExecSql(string sql)
    {
        bool ret = false;
        using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
                    trans.Commit();
                    ret = true;
                }
                catch (SqlException err)
                {
                    trans.Rollback();
                }
            }
        }
        return ret;
    }


    protected void upLaodOK0_Click(object sender, EventArgs e)
    {
        Response.Redirect("TasteList.aspx", true);
    }
    protected void upLaodOK1_Click(object sender, EventArgs e)
    {
        string pathName = HttpContext.Current.Server.MapPath("excel/") +"上传模板.xls";
        System.IO.FileInfo fileInfo = new System.IO.FileInfo(pathName);
        if (fileInfo.Exists == false)
        {
            Response.Write("<script>alert('下载文件数据为空!');</script>");
            return;
        }
        Response.Clear();
        Response.ContentType = "application/x-excel";
        Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode("上传模板.xls", System.Text.Encoding.UTF8));
        Response.WriteFile(pathName);
    }
}
